IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_GetStaffForLoginPage')
          AND TYPE IN ('P','PC'))
   DROP PROCEDURE USP_GetStaffForLoginPage
GO

/****** Object:  StoredProcedure [dbo].[USP_GetStaffForLoginPage]    Script Date: 10/20/2010 15:51:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Boonyarit
-- Create date: 26/11/2010
-- exec[USP_GetStaffForLoginPage]
-- =============================================
CREATE PROCEDURE [dbo].[USP_GetStaffForLoginPage]
	-- Add the parameters for the stored procedure here
	@staff_no nvarchar(20)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	declare @staff_code as int
	
	select @staff_code = staff_code from staff where staff_no = @staff_no 

	SELECT s.staff_code, staff_no, staff_password, staff_password_salt, staff_fail_login, staff_last_login, isnull(staff_status_login,0) staff_status_login,
	s.staff_firstname, s.staff_lastname, s.department_code, d.department_name, d.department_parent, d.department_number,
	s.group_user_code, g.group_user_name, g.group_user_type, m.master_department_name,
	m.master_department_level, s.staff_status, g.group_user_status, d.department_status,
	g.group_user_password_expire,
	CASE WHEN p.last_change_password IS NULL THEN (s.staff_register_date + g.group_user_password_expire) ELSE (p.last_change_password + g.group_user_password_expire) END next_expired_date
	FROM staff s
	INNER JOIN group_user g ON s.group_user_code = g.group_user_code
	INNER JOIN stp_department d ON s.department_code = d.department_code
	INNER JOIN master_department m ON d.master_department_code = m.master_department_code
	LEFT JOIN	(select MAX(create_datetime) last_change_password, staff_code
	from log_password where staff_code = @staff_code group by staff_code)
	p on s.staff_code = p.staff_code
	WHERE s.staff_no = @staff_no

END

GO
